//产品出货单后端接口
var express = require('express');
var Msdb = require("../../../db/Mysqldb.js");
const multer = require('multer');
const path = require('path');
var router = express.Router();
const fs = require('fs'); // 引入fs模块检查目录
const crypto = require('crypto');

// 确保sales目录存在
const salesDir = path.join(__dirname, '../../../../productDelivery/');
if (!fs.existsSync(salesDir)) {
    fs.mkdirSync(salesDir, { recursive: true })
}

// 配置multer存储
const storage = multer.diskStorage({
    destination: (_req, _file, cb) => {
        cb(null, salesDir);
    },
    filename: (req, file, cb) => {
        const timeStp = Date.now();
        const ext = path.extname(file.originalname);
        // 生成32位随机字符串
        const randomStr = crypto.randomBytes(16).toString('hex');
        const filename = `${req.auth.user_id||req.auth.user_id}_${timeStp}_${randomStr}${ext}`
        cb(null, filename);
    }
});

const upload = multer({ storage });

var db = new Msdb();
var getTime = () => {
    return new Date(new Date().getTime() + 8 * 60 * 60 * 1000).toISOString().slice(0, 19).replace('T', ' ');
}

router.post("/uploadOrder", upload.fields([
    { name: 'delivery_list', maxCount: 1 },
    { name: 'factory_acceptance_report', maxCount: 1 },
    { name: 'shipment_plan', maxCount: 1 },
    { name: 'product_shipment_order', maxCount: 1 },
    { name: 'customer_acceptance_report', maxCount: 1 }
]), async function(req, res) {
    // 获取数据库连接
    const connection = await db.getConnection();
    let result = {};
    const uploadedFiles = []; // 记录已上传文件路径
    
    try {
        // 1. 获取表单数据
        const data = { ...req.body };
        
        // 解析产品列表数据
        const products = JSON.parse(data.products || '[]');
        console.log('产品列表信息:', products);
        const productIds = products.map(product => product.id).filter(Boolean);
        console.log('产品ID列表:', productIds);
        
        // 2. 检查出货单号是否重复
        const [orders] = await connection.execute(
            `SELECT id FROM product_shipment_order WHERE shipment_order_id=?`, 
            [data.shipment_order_id]
        );
        
        if (orders.length !== 0) {
            // 删除已保存的附件
            if (req.files) {
                Object.keys(req.files).forEach(fileField => {
                    req.files[fileField].forEach(file => {
                        const filePath = path.join(salesDir, file.filename);
                        if (fs.existsSync(filePath)) {
                            fs.unlinkSync(filePath);
                            uploadedFiles.push(filePath);
                        }
                    });
                });
            }
            
            result = {
                code: 201,
                msg: "出货单号重复"
            };
            res.json(result);
            return;
        }
        
        // 3. 验证SN码唯一性
        // 3.1 检查当前产品列表中是否有重复的SN码
        const nonEmptySns = products
            .map(p => p.sn?.trim())
            .filter(sn => sn); // 过滤空值
            
        const duplicateInList = nonEmptySns.filter((sn, index) => 
            nonEmptySns.indexOf(sn) !== index
        );
        
        if (duplicateInList.length > 0) {
            throw new Error(`产品列表中存在重复的SN码: ${[...new Set(duplicateInList)].join(', ')}`);
        }
        
        // 3.2 检查数据库中是否已存在相同的SN码
        if (nonEmptySns.length > 0) {
            // 创建与SN码数量匹配的占位符 (?, ?, ...)
            const placeholders = nonEmptySns.map(() => '?').join(',');
            const [existingProducts] = await connection.execute(
                `SELECT sn, id FROM product WHERE sn IN (${placeholders})`,
                nonEmptySns
            );
            
            if (existingProducts.length > 0) {
                const existingSns = existingProducts.map(p => 
                    `SN码【${p.sn}】已被产品ID【${p.id}】使用`
                );
                throw new Error(`系统中已存在相同的SN码: ${existingSns.join('; ')}`);
            }
        }
        
        // 4. 处理交付清单 - 优先处理引用单号
        if (data.delivery_list_reference) {
            const [refDeliveryOrders] = await connection.execute(
                `SELECT delivery_list FROM product_shipment_order WHERE shipment_order_id=?`, 
                [data.delivery_list_reference]
            );
            
            if (refDeliveryOrders.length === 0) {
                throw new Error(`交付清单引用的单号【${data.delivery_list_reference}】不存在`);
            }
            
            const refDeliveryList = refDeliveryOrders[0].delivery_list;
            if (!refDeliveryList) {
                throw new Error(`交付清单引用的单号【${data.delivery_list_reference}】没有对应的文件`);
            }
            
            data.delivery_list = refDeliveryList;
        } 
        else if (req.files['delivery_list']) {
            const file = req.files['delivery_list'][0];
            data.delivery_list = file.filename;
        }
        
        // 5. 处理出厂验收报告 - 优先处理引用单号
        if (data.factory_acceptance_report_reference) {
            const [refFactoryOrders] = await connection.execute(
                `SELECT factory_acceptance_report FROM product_shipment_order WHERE shipment_order_id=?`, 
                [data.factory_acceptance_report_reference]
            );
            
            if (refFactoryOrders.length === 0) {
                throw new Error(`出厂验收报告引用的单号【${data.factory_acceptance_report_reference}】不存在`);
            }
            
            const refFactoryReport = refFactoryOrders[0].factory_acceptance_report;
            if (!refFactoryReport) {
                throw new Error(`出厂验收报告引用的单号【${data.factory_acceptance_report_reference}】没有对应的文件`);
            }
            
            data.factory_acceptance_report = refFactoryReport;
        } 
        else if (req.files['factory_acceptance_report']) {
            const file = req.files['factory_acceptance_report'][0];
            data.factory_acceptance_report = file.filename;
        }
        
        // 6. 处理出货计划 - 优先处理引用单号
        if (data.shipment_plan_reference) {
            const [refShipmentOrders] = await connection.execute(
                `SELECT shipment_plan FROM product_shipment_order WHERE shipment_order_id=?`, 
                [data.shipment_plan_reference]
            );
            
            if (refShipmentOrders.length === 0) {
                throw new Error(`出货计划引用的单号【${data.shipment_plan_reference}】不存在`);
            }
            
            const refShipmentPlan = refShipmentOrders[0].shipment_plan;
            if (!refShipmentPlan) {
                throw new Error(`出货计划引用的单号【${data.shipment_plan_reference}】没有对应的文件`);
            }
            
            data.shipment_plan = refShipmentPlan;
        } 
        else if (req.files['shipment_plan']) {
            const file = req.files['shipment_plan'][0];
            data.shipment_plan = file.filename;
        }
        
        // 7. 处理产品出货单 - 优先处理引用单号
        if (data.product_shipment_order_reference) {
            const [refProductOrders] = await connection.execute(
                `SELECT product_shipment_order FROM product_shipment_order WHERE shipment_order_id=?`, 
                [data.product_shipment_order_reference]
            );
            
            if (refProductOrders.length === 0) {
                throw new Error(`产品出货单引用的单号【${data.product_shipment_order_reference}】不存在`);
            }
            
            const refProductOrder = refProductOrders[0].product_shipment_order;
            if (!refProductOrder) {
                throw new Error(`产品出货单引用的单号【${data.product_shipment_order_reference}】没有对应的文件`);
            }
            
            data.product_shipment_order = refProductOrder;
        } 
        else if (req.files['product_shipment_order']) {
            const file = req.files['product_shipment_order'][0];
            data.product_shipment_order = file.filename;
        }
        
        // 8. 处理客户验收报告 - 优先处理引用单号
        if (data.customer_acceptance_report_reference) {
            const [refCustomerOrders] = await connection.execute(
                `SELECT customer_acceptance_report FROM product_shipment_order WHERE shipment_order_id=?`, 
                [data.customer_acceptance_report_reference]
            );
            
            if (refCustomerOrders.length === 0) {
                throw new Error(`客户验收报告引用的单号【${data.customer_acceptance_report_reference}】不存在`);
            }
            
            const refCustomerReport = refCustomerOrders[0].customer_acceptance_report;
            if (!refCustomerReport) {
                throw new Error(`客户验收报告引用的单号【${data.customer_acceptance_report_reference}】没有对应的文件`);
            }
            
            data.customer_acceptance_report = refCustomerReport;
        } 
        else if (req.files['customer_acceptance_report']) {
            const file = req.files['customer_acceptance_report'][0];
            data.customer_acceptance_report = file.filename;
        }
        
        // 统一处理空值
        const formattedData = {
            shipment_order_id: data.shipment_order_id || null,
            delivery_address: data.delivery_address || null,
            shipment_date: data.shipment_date || null,
            sales_order_id: data.sales_order_id || null,
            delivery_list: data.delivery_list || null,
            factory_acceptance_report: data.factory_acceptance_report || null,
            shipment_plan: data.shipment_plan || null,
            product_shipment_order: data.product_shipment_order || null,
            customer_acceptance_report: data.customer_acceptance_report || null,
            delivery_list_reference: data.delivery_list_reference || null,
            factory_acceptance_report_reference: data.factory_acceptance_report_reference || null,
            shipment_plan_reference: data.shipment_plan_reference || null,
            product_shipment_order_reference: data.product_shipment_order_reference || null,
            customer_acceptance_report_reference: data.customer_acceptance_report_reference || null,
            insert_user_id: req.auth.user_id,
            create_time: getTime()
        };
        
        // 验证必要参数
        if (!formattedData.shipment_order_id) {
            throw new Error("出货单号不能为空");
        }
        if (products.length === 0) {
            throw new Error("产品列表不能为空");
        }
        
        // 开启事务
        await db.beginTransaction(connection);
        
        // 新增出货单
        await connection.execute(
            `INSERT INTO product_shipment_order (
                shipment_order_id, delivery_address, shipment_date, 
                sales_order_id, delivery_list, factory_acceptance_report,
                shipment_plan, product_shipment_order, customer_acceptance_report, 
                insert_user_id, create_time
             ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
            [
                formattedData.shipment_order_id, formattedData.delivery_address, formattedData.shipment_date,
                formattedData.sales_order_id, formattedData.delivery_list, formattedData.factory_acceptance_report,
                formattedData.shipment_plan, formattedData.product_shipment_order, formattedData.customer_acceptance_report,
                formattedData.insert_user_id, formattedData.create_time
            ]
        );
        
        // 获取刚插入的出货单ID
        const [shipmentResult] = await connection.execute(
            `SELECT id FROM product_shipment_order WHERE shipment_order_id=?`,
            [formattedData.shipment_order_id]
        );
        const shipmentId = shipmentResult[0]?.id;
        
        // 处理产品SN码更新 - 新增更新shipment_order_id字段
        const productUpdateLogs = []; // 记录产品更新日志
        for (const product of products) {
            // 验证产品是否存在
            const [existingProducts] = await connection.execute(
                `SELECT id FROM product WHERE id = ?`,
                [product.id]
            );
            
            if (existingProducts.length === 0) {
                throw new Error(`产品ID【${product.id}】不存在，无法更新SN码和出货单号`);
            }
            
            // 更新产品SN码、出货单号及相关字段
            await connection.execute(
                `UPDATE product 
                 SET sn = ?, 
                     shipment_order_id = ?,
                     update_user_id = ?, 
                     update_time = ?,
                     station_id = ?
                 WHERE id = ?`,
                [
                    product.sn?.trim() || null,  // 保留空值（允许后续补充）
                    formattedData.shipment_order_id,  // 新增：设置出货单号
                    formattedData.insert_user_id,  // 更新操作人
                    formattedData.create_time,     // 更新时间
                    product.station_id || null,
                    product.id,                     // 产品ID
                ]
            );
            
            // 记录更新信息（包含出货单号）
            productUpdateLogs.push(
                `产品ID: ${product.id}, 出货单号: ${formattedData.shipment_order_id}, ${product.sn ? `SN码更新为: ${product.sn}` : '未设置SN码'}
                ,${product.station_id ? `站点更新为: ${product.station_id}` : '未设置站点'}`
            );
        }
        
        // 生成日志信息（包含产品更新记录）
        const logFields = [
            `出货单号: ${formattedData.shipment_order_id}`,
            `送货地址: ${formattedData.delivery_address}`,
            formattedData.shipment_date ? `出货日期: ${formattedData.shipment_date}` : '',
            formattedData.sales_order_id ? `销售订单号: ${formattedData.sales_order_id}` : '',
            `产品数量: ${products.length}个`,
            ...productUpdateLogs,  // 追加产品更新记录
            formattedData.delivery_list ? `交付清单: 已上传` : '',
            formattedData.factory_acceptance_report ? `出厂验收报告: 已上传` : '',
            formattedData.shipment_plan ? `出货计划: 已上传` : '',
            formattedData.product_shipment_order ? `产品出货单: 已上传` : '',
            formattedData.customer_acceptance_report ? `客户验收报告: 已上传` : ''
        ].filter(Boolean).join('; ');
        
        const logMessage = `用户ID: ${formattedData.insert_user_id} 新增产品出货单，${logFields}`;
        
        await connection.execute(
            `INSERT INTO operation_log (user_id, sql_words, time) VALUES (?, ?, ?);`,
            [formattedData.insert_user_id, logMessage, formattedData.create_time]
        );
        
        // 提交事务
        await db.commit(connection);
        
        result = {
            code: 200,
            msg: "上传成功",
            data: {
                shipmentId,
                productCount: products.length,
                updatedProductIds: productIds,  // 返回更新的产品ID列表
                shipment_order_id: formattedData.shipment_order_id  // 返回出货单号
            }
        };
    } catch (error) {
        // 发生错误时回滚事务
        await db.rollback(connection);
        console.error("上传处理出错:", error);
        
        // 回滚时删除已上传的文件
        if (req.files) {
            Object.keys(req.files).forEach(fileField => {
                req.files[fileField].forEach(file => {
                    const filePath = path.join(salesDir, file.filename);
                    if (fs.existsSync(filePath)) {
                        fs.unlinkSync(filePath);
                        console.log(`事务回滚，删除文件: ${filePath}`);
                    }
                });
            });
        }
        
        result = {
            code: error.message ? 400 : 500,
            msg: error.message || "服务器内部错误"
        };
    } finally {
        // 确保连接释放
        connection.release();
    }
    
    res.json(result);
});

router.post("/updateOrder", upload.fields([
    { name: 'delivery_list', maxCount: 1 },
    { name: 'factory_acceptance_report', maxCount: 1 },
    { name: 'shipment_plan', maxCount: 1 },
    { name: 'product_shipment_order', maxCount: 1 },
    { name: 'customer_acceptance_report', maxCount: 1 }
]), async function(req, res) {
    // 获取数据库连接
    const connection = await db.getConnection();
    let result = {}
    
    try {
        // 1. 验证更新用户ID
        const data = { ...req.body };
        if (!req.auth.user_id) {
            result = { code: 400, msg: "没有更新用户ID，不允许更新" };
            return res.json(result);
        }
        
        // 2. 处理上传文件
        const fileFields = [
            'delivery_list', 'factory_acceptance_report', 'shipment_plan',
            'product_shipment_order', 'customer_acceptance_report'
        ];
        fileFields.forEach(field => {
            if (req.files?.[field]?.[0]) {
                data[field] = req.files[field][0].filename;
            }
        });
        
        // 3. 设置字段默认值（已移除产品相关字段）
        const fieldsWithDefaultNull = [
            'shipment_order_id', 'delivery_address', 'shipment_date', 
            'sales_order_id', ...fileFields
        ];
        fieldsWithDefaultNull.forEach(field => {
            data[field] = data[field] || null;
        });
        
        // 4. 查询原始数据用于比较
        const [originalRows] = await connection.execute(
            'SELECT * FROM product_shipment_order WHERE id = ?',
            [data.id]
        );
        
        if (originalRows.length === 0) {
            result = { code: 404, msg: "订单记录不存在" };
            return res.json(result);
        }
        const originalData = originalRows[0];
        
        // 5. 比较字段变化（已移除产品相关字段）
        const fieldsToCheck = [
            'shipment_date', 'sales_order_id', ...fileFields
        ];
        
        const changes = [];
        const updateFields = [];
        const updateValues = [];
        
        // 检查每个字段是否有变化
        fieldsToCheck.forEach(field => {
            const newValue = data[field];
            const oldValue = originalData[field];
            
            // 比较字段值是否不同
            const isDifferent = JSON.stringify(newValue) !== JSON.stringify(oldValue);
            
            if (isDifferent) {
                changes.push({
                    field,
                    oldValue: oldValue ?? '空',
                    newValue: newValue ?? '空'
                });
                updateFields.push(`${field} = ?`);
                updateValues.push(newValue);
            }
        });
        
        // 6. 没有变化则不执行更新
        if (changes.length === 0) {
            result = { code: 200, msg: "没有需要更新的内容" };
            return res.json(result);
        }
        
        // 7. 执行更新
        updateFields.push('update_time = ?', 'update_user_id = ?');
        updateValues.push(getTime(), req.auth.user_id, data.id);
        
        // 开启事务
        await db.beginTransaction(connection);
        
        // 执行更新
        await connection.execute(
            `UPDATE product_shipment_order SET ${updateFields.join(', ')} WHERE id = ?`,
            updateValues
        );
        
        // 8. 记录操作日志
        const changeDescription = changes.map(change => 
            `${change.field}: ${change.oldValue} → ${change.newValue}`
        ).join('; ');
        
        const logMessage = `用户ID: ${req.auth.user_id} 更新了产品出货单ID: ${data.id}，变更内容: ${changeDescription}`;
        
        await connection.execute(
            `INSERT INTO operation_log (user_id, sql_words, time) VALUES (?, ?, ?)`,
            [req.auth.user_id, logMessage, getTime()]
        );
        
        // 提交事务
        await db.commit(connection);
        
        result = {
            code: 200,
            msg: "更新成功",
            changes: changes
        };
    } catch (error) {
        // 发生错误时回滚事务
        await db.rollback(connection);
        
        // 回滚时删除新上传的文件
        if (req.files) {
            Object.keys(req.files).forEach(fileField => {
                req.files[fileField].forEach(file => {
                    const filePath = path.join(salesDir, file.filename);
                    if (fs.existsSync(filePath)) {
                        fs.unlinkSync(filePath);
                        console.log(`事务回滚，删除文件: ${filePath}`);
                    }
                });
            });
        }
        
        result = {
            code: 500,
            msg: "服务器内部错误"
        };
    } finally {
        // 确保连接释放
        connection.release();
    }
    
    res.json(result);
});

router.post('/searchProductDelivery',async function(req,res){//搜索产品出货单
    var result={}
    var data=req.body
    var sql="SELECT * FROM product_shipment_order WHERE 1=1"//无限制查询语句
    var getTotalSql="SELECT COUNT(*) AS total FROM product_shipment_order WHERE 1=1"//获取总数语句
    var params=[]//参数
    for(const key in data.condition){//遍历查询条件
        if(data.condition[key]!=null){
            sql+=` AND ${key}=?`
            getTotalSql+=` AND ${key}=?`
            params.push(data.condition[key])
        }
    }
    var [total]=await db.query(getTotalSql,params)
    sql+=" LIMIT ? OFFSET ?"//进行分页操作，分别为页大小与页偏移
    params.push(data.pageSize)
    params.push(data.offset)
    var productDeliveryList=await db.query(sql,params)
    result={
        data:productDeliveryList,
        code:200,
        msg:"查询成功~",
        total:total.total
    }
    res.send(JSON.stringify(result))
})

router.post('/getProductDeliveryInfo',async function(req,res){//搜索具体的产品出货单
    var result={}
    var data=req.body
    var sql="SELECT * FROM product_shipment_order WHERE id=?"
    var productShipment=await db.query(sql,[data.id])
    result={
        data:productShipment[0],
        code:200,
        msg:"查询成功~"
    }
    res.send(JSON.stringify(result))
})

// 错误处理中间件
router.use((err, req, res, next) => {
    if (err instanceof multer.MulterError) {
        console.error("Multer文件上传错误:", err);
        return res.status(500).json({ 
            code: 500, 
            msg: `文件上传错误: ${err.message}`
        });
    }
    next(err);
});

module.exports = router;